/*******************************************************************************
 * Copyright (c) 2012 Handypages.nl
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *  http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *******************************************************************************/
package nl.handypages.trviewer.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class GenericDatabaseHelper extends SQLiteOpenHelper {
	private static final int DATABASE_VERSION = 26; // This is application version not sqlite version, increase to force onUpgrade  
	private static final String DATABASE_NAME = "actionlist_config.db";
	/*
	 * SQL Create statements below. Foreign keys can not be added by an ALTER statement
	 * (like generated by ERMaster), but must be included in the create statement.
	 * FK's are also not enforced by default, see PRAGMA foreign_keys=ON; in onOpen()
	 * to fix this at runtime.
	 */
	
	private static final String TABLE_ACTIONLISTS_CREATE = "CREATE TABLE ACTIONLISTS" +
			"(LISTNAME TEXT NOT NULL UNIQUE," +
			"WEIGHT INTEGER NOT NULL," +
			"CUSTOM INTEGER NOT NULL," +
			"FILTERDONE INTEGER NOT NULL, PRIMARY KEY (LISTNAME));";
	// The word ORDER can not be used instead of WEIGHT; because it is a reserved word by sqlite.
	
	private static final String TABLE_FILTER_CONTEXT_CREATE = "CREATE TABLE FILTER_CONTEXT" +
			"(LISTNAME TEXT NOT NULL, CONTEXT_ID TEXT NOT NULL," +
			"PRIMARY KEY (LISTNAME, CONTEXT_ID) FOREIGN KEY (LISTNAME) " +
			"REFERENCES ACTIONLISTS (LISTNAME) ON UPDATE CASCADE ON DELETE CASCADE);";

	private static final String TABLE_FILTER_STATUS_CREATE = "CREATE TABLE FILTER_STATUS" +
			"(LISTNAME TEXT NOT NULL, STATUS_ID TEXT NOT NULL," +
			"PRIMARY KEY (LISTNAME, STATUS_ID) FOREIGN KEY (LISTNAME) " +
			"REFERENCES ACTIONLISTS (LISTNAME) ON UPDATE CASCADE ON DELETE CASCADE);";
	
	private static final String TABLE_FILTER_TOPIC_CREATE = "CREATE TABLE FILTER_TOPIC " +
			"(LISTNAME TEXT NOT NULL, TOPIC_ID TEXT," +
			"PRIMARY KEY (LISTNAME, TOPIC_ID) FOREIGN KEY (LISTNAME) " +
			"REFERENCES ACTIONLISTS (LISTNAME) ON UPDATE CASCADE ON DELETE CASCADE);";
	
	private static final String TABLE_FILTER_ACTION_DATE_CREATE = "CREATE TABLE FILTER_ACTION_DATE" +
			"(LISTNAME TEXT NOT NULL, ACTION_DATE_ID TEXT, ACTION_DATE_VALUE TEXT, ACTION_DATE_TYPE TEXT," +
			"PRIMARY KEY (LISTNAME, ACTION_DATE_ID) FOREIGN KEY (LISTNAME) " +
			"REFERENCES ACTIONLISTS (LISTNAME) ON UPDATE CASCADE ON DELETE CASCADE);";

	private static final String TABLE_SORT_CREATE = "CREATE TABLE SORT " +
			"(LISTNAME TEXT NOT NULL, CRITERIA INTEGER NOT NULL, DIRECTION INTEGER, " +
			"WEIGHT INTEGER, " +
			"PRIMARY KEY (LISTNAME, CRITERIA), " +
			"FOREIGN KEY (LISTNAME) REFERENCES ACTIONLISTS (LISTNAME) ON UPDATE CASCADE ON DELETE CASCADE);";
			
	private static final String TABLE_ACTIONS_CREATE = "CREATE TABLE ACTIONS " +
			"(ID TEXT NOT NULL, CREATED INTEGER, DESCRIPTION TEXT, " +
			"TOPICINDEX TEXT, TOPICID TEXT, CONTEXTINDEX TEXT, CONTEXTID TEXT," +
			"STATE TEXT, NOTES TEXT, DONE TEXT, MODIFIED INTEGER, DUEDATE INTEGER," +
			"SCHEDULEDDATE INTEGER, SCHEDULEDRECURRING TEXT, DELEGATEDTO TEXT, CHASEDATE INTEGER," +
			"PROJECTID TEXT NOT NULL," +
			"PRIMARY KEY (ID));";
	
	private static final String TABLE_TOPICS_CREATE = "CREATE TABLE TOPICS " +
			"(ID TEXT NOT NULL, NAME TEXT, TOPICINDEX INTEGER, " +
			"DESCRIPTION TEXT," +
			"PRIMARY KEY (ID));";
	
	private static final String TABLE_CONTEXTS_CREATE = "CREATE TABLE CONTEXTS " +
			"(ID TEXT NOT NULL, NAME TEXT, CONTEXTINDEX INTEGER, " +
			"DESCRIPTION TEXT," +
			"PRIMARY KEY (ID));";
	
	private static final String TABLE_ACTORS_CREATE = "CREATE TABLE ACTORS " +
			"(ID TEXT NOT NULL, NAME TEXT, EMAIL TEXT, " +
			"INACTIVE INTEGER NOT NULL," +
			"PRIMARY KEY (ID));";
	
	private static final String TABLE_PROJECTS_CREATE = "CREATE TABLE PROJECTS " +
			"(ID TEXT NOT NULL, PARENTID TEXT NOT NULL, DONE INTEGER NOT NULL, " +
			"DESCRIPTION TEXT, TYPE TEXT," +
			"PRIMARY KEY (ID));";
	
	public GenericDatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Method is called during creation of the database
	@Override
	public void onCreate(SQLiteDatabase database) {
		database.execSQL(TABLE_ACTIONLISTS_CREATE);
		database.execSQL(TABLE_FILTER_TOPIC_CREATE);
		database.execSQL(TABLE_FILTER_CONTEXT_CREATE);
		database.execSQL(TABLE_FILTER_STATUS_CREATE);
		database.execSQL(TABLE_FILTER_ACTION_DATE_CREATE);
		database.execSQL(TABLE_SORT_CREATE);
		database.execSQL(TABLE_ACTIONS_CREATE);
		database.execSQL(TABLE_TOPICS_CREATE);
		database.execSQL(TABLE_CONTEXTS_CREATE);
		database.execSQL(TABLE_ACTORS_CREATE);
		database.execSQL(TABLE_PROJECTS_CREATE);
		
	}

	// Method is called during an upgrade of the database, e.g. if you increase
	// the database version
	@Override
	public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
		Log.w(GenericDatabaseHelper.class.getName(),
				"Upgrading database from version " + oldVersion + " to "
						+ newVersion + ", which will destroy all old data");
		database.execSQL("DROP TABLE IF EXISTS FILTER_ACTION_DATE");
		database.execSQL("DROP TABLE IF EXISTS FILTER_CONTEXT");
		database.execSQL("DROP TABLE IF EXISTS FILTER_STATUS");
		database.execSQL("DROP TABLE IF EXISTS FILTER_TOPIC");
		database.execSQL("DROP TABLE IF EXISTS SORT");
		database.execSQL("DROP TABLE IF EXISTS ACTIONLISTS");
		database.execSQL("DROP TABLE IF EXISTS ACTIONS");
		database.execSQL("DROP TABLE IF EXISTS TOPICS");
		database.execSQL("DROP TABLE IF EXISTS CONTEXTS");
		database.execSQL("DROP TABLE IF EXISTS ACTORS");
		database.execSQL("DROP TABLE IF EXISTS PROJECTS");
		
		onCreate(database);
	}
	
	@Override
	public void onOpen(SQLiteDatabase db) {
		super.onOpen(db);
		if (!db.isReadOnly())
	    {
	      /*
	       *  Enable foreign key constraints (cascade delete)
	       *  Only works since Android 2.2 (Level 8)
	       */
	      db.execSQL("PRAGMA foreign_keys=ON;");
	    }

	}
}